package com.CuandoLlegaAndroid;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import java.text.DateFormat;
import java.util.ArrayList;
import java.util.Date;

import com.CuandoLlegaAndroid.DDLModels.Favourite;
import com.CuandoLlegaAndroid.DDLModels.SortSetting;
import com.CuandoLlegaAndroid.DDLModels.Street;

public class DataHelper {

	private static final String DATABASE_NAME = "favourites.db";
	private static final int DATABASE_VERSION = 3;
	private static final String TABLE_FAVOURITE = "favourite";
	private static final String TABLE_STREET = "street";
	private static final String TABLE_INTERSECT = "intersection";
	private static final String TABLE_STOP = "stop";

	private Context context;
	private SQLiteDatabase db; 

	private SQLiteStatement insertFavouriteStmt;
	private SQLiteStatement updateFavouriteStmt;
	private SQLiteStatement deleteFavouriteStmt;

	private SQLiteStatement insertStreetStmt;
	private SQLiteStatement deleteStreetStmt;

	private SQLiteStatement insertIntersectStmt;
	private SQLiteStatement deleteIntersectStmt;

	private SQLiteStatement insertStopStmt;
	private SQLiteStatement deleteStopStmt;

	private static final String INSERTFAVOURITE = "insert into " 
		+ TABLE_FAVOURITE + "(name, line,nro,result,date,street,inters,descrip,locGPS) values (?,?,?,?,?,?,?,?,?)";

	private static final String UPDATEFAVOURITE = "Update " 
		+ TABLE_FAVOURITE + " SET name = ?, line = ?, nro = ?, result = ? ,date = ?, descrip = ? where id = ?";

	private static final String DELETEFAVOURITE = "Delete from " 
		+ TABLE_FAVOURITE + " where id = ?";

	private static final String INSERTSTREET = "insert into " 
		+ TABLE_STREET + "(idStreet,nameStreet,idLine,nameLine) values (?,?,?,?)";

	private static final String DELETESTREET = "Delete from " 
		+ TABLE_STREET + " where idLine = ?";

	private static final String INSERTINTERSECT = "insert into " 
		+ TABLE_INTERSECT + "(idStreet,nameStreet,idLine,nameLine,idIntersect,nameIntersect) values (?,?,?,?,?,?)";

	private static final String DELETEINTERSECT = "Delete from " 
		+ TABLE_INTERSECT + " where idLine = ? and idStreet = ?";

	private static final String INSERTSTOP = "insert into " 
		+ TABLE_STOP + "(idStreet,nameStreet,idLine,nameLine,idIntersect,nameIntersect,nroStop, textStop) values (?,?,?,?,?,?,?,?)";

	private static final String DELETESTOP = "Delete from " 
		+ TABLE_STOP + "  where idLine = ? and idStreet = ? and idIntersect = ?";


	/**
	 * Public constructor for Data Helper
	 * @param context
	 */
	public DataHelper(Context context) {
		this.context = context;
		OpenHelper openHelper = new OpenHelper(this.context);
		this.db = openHelper.getWritableDatabase();

		this.insertFavouriteStmt = this.db.compileStatement(INSERTFAVOURITE);
		this.updateFavouriteStmt= this.db.compileStatement(UPDATEFAVOURITE);
		this.deleteFavouriteStmt = this.db.compileStatement(DELETEFAVOURITE);

		this.insertStreetStmt = this.db.compileStatement(INSERTSTREET);
		this.deleteStreetStmt= this.db.compileStatement(DELETESTREET);

		this.insertIntersectStmt = this.db.compileStatement(INSERTINTERSECT);
		this.deleteIntersectStmt = this.db.compileStatement(DELETEINTERSECT);

		this.insertStopStmt = this.db.compileStatement(INSERTSTOP);
		this.deleteStopStmt = this.db.compileStatement(DELETESTOP);
	}

	/**
	 * A custom static class to manage database creation and version
	 */
	private static class OpenHelper extends SQLiteOpenHelper {

		/**
		 * Constructor for Open Helper
		 * @param context
		 */
		OpenHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		/**
		 * Method to create Favourite Table
		 * @param db
		 */
		private void createFavouriteTable(SQLiteDatabase db)
		{
			StringBuilder sb = new StringBuilder();
			sb.append("CREATE TABLE ");
			sb.append(TABLE_FAVOURITE);
			sb.append("(");
			sb.append("id INTEGER PRIMARY KEY, ");
			sb.append("name TEXT, ");
			sb.append("line TEXT, ");
			sb.append("nro TEXT, ");
			sb.append("result TEXT, ");
			sb.append("date TEXT, ");
			sb.append("street TEXT, ");
			sb.append("inters TEXT, ");
			sb.append("descrip TEXT, ");
			sb.append("locGPS TEXT ");
			sb.append(")");

			db.execSQL(sb.toString());
		}

		/**
		 * Method to re create Favourite Table
		 * @param db
		 */
		private void reCreateFavouriteTable(SQLiteDatabase db)
		{
			ArrayList<Favourite> list = new ArrayList<Favourite>();

			//get all data from favourite table
			Cursor cursor = db.query(TABLE_FAVOURITE, new String[] { "id, name, line, nro, result, date" }, 
					null, null, null, null, "name desc");
			if (cursor.moveToFirst()) {
				do {
					list.add(new Favourite(cursor.getString(0),
							cursor.getString(1),
							cursor.getString(2),
							cursor.getString(3),
							cursor.getString(4),
							cursor.getString(5),"","","","")); 
				} while (cursor.moveToNext());
			}
			if (cursor != null && !cursor.isClosed()) {
				cursor.close();
			} 

			//re create favourite table
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_FAVOURITE);
			onCreate(db);

			//insert all data in new table re created
			SQLiteStatement insertStmtUpdate = db.compileStatement(INSERTFAVOURITE);
			for (Favourite f : list) {
				String currentDateTimeString = DateFormat.getInstance().format(new Date());
				insertStmtUpdate.bindString(1, f.Name);
				insertStmtUpdate.bindString(2, f.Line);
				insertStmtUpdate.bindString(3, f.Nro);
				insertStmtUpdate.bindString(4,  f.Result);
				insertStmtUpdate.bindString(5, currentDateTimeString);
				insertStmtUpdate.bindString(6, "");
				insertStmtUpdate.bindString(7, "");
				insertStmtUpdate.bindString(8, "");
				insertStmtUpdate.bindString(9, "");

				insertStmtUpdate.executeInsert();
			}
		}

		/**
		 * Method to create table by cache
		 * @param db
		 */
		private void createCacheTables(SQLiteDatabase db)
		{
			StringBuilder sb = new StringBuilder();
			sb = new StringBuilder();
			sb.append("CREATE TABLE ");
			sb.append(TABLE_STREET);
			sb.append("(");
			sb.append("id INTEGER PRIMARY KEY, ");
			sb.append("idStreet TEXT, ");
			sb.append("nameStreet TEXT, ");
			sb.append("idLine TEXT, ");
			sb.append("nameLine TEXT ");
			sb.append(")");

			db.execSQL(sb.toString());

			sb = new StringBuilder();
			sb.append("CREATE TABLE ");
			sb.append(TABLE_INTERSECT);
			sb.append("(");
			sb.append("id INTEGER PRIMARY KEY, ");
			sb.append("idIntersect TEXT, ");
			sb.append("nameIntersect TEXT, ");
			sb.append("idLine TEXT, ");
			sb.append("nameLine TEXT, ");
			sb.append("idStreet TEXT, ");
			sb.append("nameStreet TEXT ");
			sb.append(")");

			db.execSQL(sb.toString());

			sb = new StringBuilder();
			sb.append("CREATE TABLE ");
			sb.append(TABLE_STOP);
			sb.append("(");
			sb.append("id INTEGER PRIMARY KEY, ");
			sb.append("nroStop TEXT, ");
			sb.append("textStop TEXT, ");
			sb.append("idIntersect TEXT, ");
			sb.append("nameIntersect TEXT, ");
			sb.append("idLine TEXT, ");
			sb.append("nameLine TEXT, ");
			sb.append("idStreet TEXT, ");
			sb.append("nameStreet TEXT");
			sb.append(")");

			db.execSQL(sb.toString());
		}

		@Override
		public void onCreate(SQLiteDatabase db) {

			//create main table (favourite)
			this.createFavouriteTable(db);

			//create cache tables
			this.createCacheTables(db);	    	  

		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

			try{
				if(newVersion==2){
					//recreate favourite table with news columns
					this.reCreateFavouriteTable(db);
				}

				if(newVersion==3)
				{
					//create cache tables
					this.createCacheTables(db);
				}
			}
			catch(Exception e)
			{
				//on exception recreate
				try{
					db.execSQL("DROP TABLE IF EXISTS " + TABLE_INTERSECT);
					db.execSQL("DROP TABLE IF EXISTS " + TABLE_STOP);
					db.execSQL("DROP TABLE IF EXISTS " + TABLE_STREET);

					this.reCreateFavouriteTable(db);
					this.createCacheTables(db);

				}
				catch(Exception x)
				{
					//on exception recreate

					db.execSQL("DROP TABLE IF EXISTS " + TABLE_FAVOURITE);
					db.execSQL("DROP TABLE IF EXISTS " + TABLE_INTERSECT);
					db.execSQL("DROP TABLE IF EXISTS " + TABLE_STOP);
					db.execSQL("DROP TABLE IF EXISTS " + TABLE_STREET);

					this.onCreate(db);
				}
			}

		}
	}

	/**
	 * Drop Favourite table
	 */
	public void dropTableFavourite(){
		this.db.execSQL("DROP TABLE IF EXISTS " + TABLE_FAVOURITE);
	}

	/**
	 * Drop Street cache table
	 */
	public void dropTableStreet()
	{
		this.db.execSQL("DROP TABLE IF EXISTS " + TABLE_STREET );
	}

	/**
	 * Drop Intersect cache table
	 */
	public void dropTableIntersect()
	{
		this.db.execSQL("DROP TABLE IF EXISTS " + TABLE_INTERSECT);
	}

	/**
	 * Drop Stop cache table
	 */
	public void dropTableStop()
	{
		this.db.execSQL("DROP TABLE IF EXISTS " + TABLE_STOP);
	}

	/**
	 * Insert into Favourite table
	 */
	public long insertFavourite(String name, String line, String nro, String result, String street, String intersect, String descrip, String loc) {

		String currentDateTimeString = DateFormat.getInstance().format(new Date());
		this.insertFavouriteStmt.bindString(1, name);
		this.insertFavouriteStmt.bindString(2, line);
		this.insertFavouriteStmt.bindString(3, nro);
		this.insertFavouriteStmt.bindString(4, result);
		this.insertFavouriteStmt.bindString(5, currentDateTimeString);
		this.insertFavouriteStmt.bindString(6, street);
		this.insertFavouriteStmt.bindString(7, intersect);
		this.insertFavouriteStmt.bindString(8, descrip);
		this.insertFavouriteStmt.bindString(9, loc);

		return this.insertFavouriteStmt.executeInsert();
	}

	/**
	 * Update Favourite table
	 */
	public void updateFavourite(Favourite fav) {

		String currentDateTimeString = DateFormat.getInstance().format(new Date());
		this.updateFavouriteStmt.bindString(1, fav.Name);
		this.updateFavouriteStmt.bindString(2, fav.Line);
		this.updateFavouriteStmt.bindString(3, fav.Nro);
		this.updateFavouriteStmt.bindString(4, fav.Result);
		this.updateFavouriteStmt.bindString(5, currentDateTimeString);
		this.updateFavouriteStmt.bindString(6, fav.Descrip);
		this.updateFavouriteStmt.bindString(7, fav.ID);

		this.updateFavouriteStmt.execute();
	}

	/**
	 * Delete all data from Favourite table
	 */
	public void deleteAllFavourite() {
		this.db.delete(TABLE_FAVOURITE, null, null);
	}

	public ArrayList<Favourite> selectFavourite(String id) {
		
		ArrayList<Favourite> list = new ArrayList<Favourite>();

		Cursor cursor = this.db.query(TABLE_FAVOURITE, new String[] { "id, name, line, nro, result, date, street, inters, descrip, locGPS" }, 
				"id = ?", new String[]{ id }, null, null, null);
		
		if (cursor.moveToFirst()) {
			do {
				list.add(new Favourite(cursor.getString(0),
						cursor.getString(1),
						cursor.getString(2),
						cursor.getString(3),
						cursor.getString(4),
						cursor.getString(5),
						cursor.getString(6),
						cursor.getString(7),
						cursor.getString(8),
						cursor.getString(9))); 
			} while (cursor.moveToNext());
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return list;
	}
	
	/**
	 * Get all data from Favourite table
	 */
	public ArrayList<Favourite> selectAllFavourite(ArrayList<SortSetting> sortSettings) 
	{
		StringBuilder sort = new StringBuilder();
		
		for(int i=0;i<sortSettings.size();i++)
		{
			if(i!=0){
				sort.append(", ");
			}
			
			sort.append(sortSettings.get(i));
		}
		
		ArrayList<Favourite> list = new ArrayList<Favourite>();

		Cursor cursor = this.db.query(TABLE_FAVOURITE, new String[] { "id, name, line, nro, result, date, street, inters, descrip, locGPS" }, 
				null, null, null, null, sort.toString());
		if (cursor.moveToFirst()) {
			do {
				list.add(new Favourite(cursor.getString(0),
						cursor.getString(1),
						cursor.getString(2),
						cursor.getString(3),
						cursor.getString(4),
						cursor.getString(5),
						cursor.getString(6),
						cursor.getString(7),
						cursor.getString(8),
						cursor.getString(9))); 
			} while (cursor.moveToNext());
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return list;
	}

	/**
	 * Delete a favourite register from Favourite table
	 * @param fav
	 */
	public void deleteFavourite(Favourite fav) 
	{
		this.deleteFavouriteStmt.bindString(1, fav.ID);
		this.deleteFavouriteStmt.execute();

	}

	/**
	 * Insert into Streets table
	 */
	public long insertStreets(String idLine, String nameLine, ArrayList<Street> streets)
	{
		long c = 0;
		for (int i = 0; i <= streets.size() - 1; i++) 
		{
			this.insertStreetStmt.bindString(1, streets.get(i).ID);
			this.insertStreetStmt.bindString(2, streets.get(i).Name);
			this.insertStreetStmt.bindString(3, idLine);
			this.insertStreetStmt.bindString(4, nameLine);

			c +=this.insertStreetStmt.executeInsert();

		}
		return c;
	}

	/**
	 * Insert into Intersect table
	 */
	public long insertIntersectStreets(String idLine, String nameLine, String idStreet, String nameStreet, ArrayList<Street> streets)
	{
		long c = 0;
		for (int i = 0; i <= streets.size() - 1; i++) 
		{
			this.insertIntersectStmt.bindString(1, idStreet);
			this.insertIntersectStmt.bindString(2, nameStreet);
			this.insertIntersectStmt.bindString(3, idLine);
			this.insertIntersectStmt.bindString(4, nameLine);
			this.insertIntersectStmt.bindString(5, streets.get(i).ID);
			this.insertIntersectStmt.bindString(6, streets.get(i).Name);

			c +=this.insertIntersectStmt.executeInsert();

		}
		return c;
	}

	/**
	 * Insert into Stops table
	 */
	public long insertStops(String idLine, String nameLine, String idStreet, String nameStreet, String idIntersect, String nameIntersect, ArrayList<Street> streets)
	{
		long c = 0;
		for (int i = 0; i <= streets.size() - 1; i++) 
		{
			this.insertStopStmt.bindString(1, idStreet);
			this.insertStopStmt.bindString(2, nameStreet);
			this.insertStopStmt.bindString(3, idLine);
			this.insertStopStmt.bindString(4, nameLine);
			this.insertStopStmt.bindString(5, idIntersect);
			this.insertStopStmt.bindString(6, nameIntersect);

			String id = "";
			if( streets.get(i).ID!=null){
				id =streets.get(i).ID;
			}

			this.insertStopStmt.bindString(7,id);

			String texto = "";
			if( streets.get(i).Name!=null){
				texto =streets.get(i).Name;
			}

			this.insertStopStmt.bindString(8, texto);

			c +=this.insertStopStmt.executeInsert();

		}
		return c;
	}

	/**
	 * Get all data from Street table by parameter
	 * @param idLinea
	 * @return
	 */
	public ArrayList<Street> selectStreetLine(String idLinea) {
		ArrayList<Street> list = new ArrayList<Street>();

		Cursor cursor = this.db.query(TABLE_STREET, new String[] { "idStreet, nameStreet" }, 
				"idLine = ?", new String[]{idLinea}, null, null, "nameStreet asc");
		if (cursor.moveToFirst()) {
			do {
				list.add(new Street(cursor.getString(1),
						cursor.getString(0))); 
			} while (cursor.moveToNext());
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return list;
	}

	/**
	 * Get all data from Intersect table by parameter
	 * @param idLinea
	 * @param idStreet
	 * @return
	 */
	public ArrayList<Street> selectIntersectStreetLine(String idLinea, String idStreet) {
		ArrayList<Street> list = new ArrayList<Street>();

		Cursor cursor = this.db.query(TABLE_INTERSECT, new String[] { "idIntersect , nameIntersect" }, 
				"idStreet = ? AND idLine = ? ", new String[]{idStreet, idLinea}, null, null, "nameIntersect ASC");
		if (cursor.moveToFirst()) {
			do {
				list.add(new Street(cursor.getString(1),
						cursor.getString(0))); 
			} while (cursor.moveToNext());
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return list;
	}

	/**
	 * Get all data from Stop table by parameter
	 * @param idLinea
	 * @param idStreet
	 * @param idIntersect
	 * @return
	 */
	public ArrayList<Street> selectStops(String idLinea, String idStreet, String idIntersect) {
		ArrayList<Street> list = new ArrayList<Street>();

		Cursor cursor = this.db.query(TABLE_STOP, new String[] { "nroStop , textStop" }, 
				"idStreet = ? AND idLine = ? AND idIntersect = ?", new String[]{idStreet, idLinea, idIntersect}, null, null, "nroStop ASC");
		if (cursor.moveToFirst()) {
			do {
				list.add(new Street(cursor.getString(1),
						cursor.getString(0))); 
			} while (cursor.moveToNext());
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return list;
	}

	/**
	 * Delete Street register by parameter
	 * @param idLinea
	 */
	public void deleteStreet(String idLinea)
	{
		this.deleteStreetStmt.bindString(1, idLinea);
		this.deleteStreetStmt.execute();
	}

	/**
	 * Delete Intersect register by parameter
	 * @param idLinea
	 * @param idStreet
	 */
	public void deleteIntersect(String idLinea, String idStreet)
	{
		this.deleteIntersectStmt.bindString(1, idLinea);
		this.deleteIntersectStmt.bindString(2, idStreet);
		this.deleteIntersectStmt.execute();
	}

	/**
	 * Delete Stops register by parameter
	 * @param idLinea
	 * @param idStreet
	 * @param idIntersect
	 */
	public void deleteStop(String idLinea, String idStreet, String idIntersect)
	{
		this.deleteStopStmt.bindString(1, idLinea);
		this.deleteStopStmt.bindString(2, idStreet);
		this.deleteStopStmt.bindString(3, idIntersect);
		this.deleteStopStmt.execute();
	}

	/**
	 * Delete all cache table
	 */
	public void deleteAllCache()
	{
		this.db.delete(TABLE_STREET, null, null);
		this.db.delete(TABLE_INTERSECT, null, null);
		this.db.delete(TABLE_STOP, null, null);
	}

}
